package dao;

import entity.*;
import util.IRowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by Administrator on 2017/11/14.
 */
public class BaseGoodsDao {
    private DBHelper dbHelper=new DBHelper();
    //热销产品查询
    public List<BaseGoodsEntity> query() throws SQLException, ClassNotFoundException {
      String sql="SELECT g.id id,g.price price,g.good_name good_name,p.picUrl  picUrl FROM itlb_goods g LEFT JOIN \n" +
              "(SELECT good_id,SUM(buy_count) sales_count FROM itlb_order_details)s\n" +
              "ON g.id=s.good_id LEFT JOIN itlb_goods_pic p ON g.id=p.goodsId WHERE g.status_line=1 ORDER BY s.sales_count\n" +
              "LIMIT 0,6";
        IRowMapper<BaseGoodsEntity> rowMapper=new IRowMapper<BaseGoodsEntity>() {
            @Override
            public BaseGoodsEntity mapper(ResultSet rs) throws SQLException, ClassNotFoundException {
               BaseGoodsEntity bse=new BaseGoodsEntity();
                bse.setId(rs.getLong("id"));
                bse.setGood_name(rs.getString("good_name"));
                bse.setPrice(rs.getDouble("price"));
                bse.setPicUrl(rs.getString("picUrl"));
                return bse;
            }
        };
        List<BaseGoodsEntity> list=dbHelper.queryAllHelper(rowMapper,sql);
        return list;
    }
    //最新上架查询
    public List<BaseGoodsEntity> queryNew()throws ClassNotFoundException,SQLException{
        String sql="SELECT g.id id,g.price price,g.good_name good_name,p.picUrl  picUrl FROM itlb_goods g LEFT JOIN \n" +
                "\n" +
                "(SELECT good_id,SUM(buy_count) sales_count FROM itlb_order_details)s\n" +
                "   ON g.id=s.good_id LEFT JOIN itlb_goods_pic p ON g.id=p.goodsId WHERE g.status_line=1 ORDER BY\n" +
                "    g.orders_weight DESC   LIMIT 0,6";
        IRowMapper<BaseGoodsEntity> rowMapper=new IRowMapper<BaseGoodsEntity>() {
            @Override
            public BaseGoodsEntity mapper(ResultSet rs) throws SQLException, ClassNotFoundException {
                BaseGoodsEntity bse=new BaseGoodsEntity();
                bse.setId(rs.getLong("id"));
                bse.setGood_name(rs.getString("good_name"));
                bse.setPrice(rs.getDouble("price"));
                bse.setPicUrl(rs.getString("picUrl"));
                return bse;
            }
        };
        List<BaseGoodsEntity> list=dbHelper.queryAllHelper(rowMapper,sql);
        return list;
    }
    //查询货物详情
    public DetailEntity queryDetail(long goodsid)throws ClassNotFoundException,SQLException{
        String sql="SELECT id,price,good_name,detail FROM itlb_goods WHERE id=?";
        ResultSet rs=dbHelper.executequery(sql,goodsid);
        DetailEntity de=new DetailEntity();
        if (rs.next()){
            de.setId(rs.getLong("id"));
            de.setPrice(rs.getDouble("price"));
            de.setGood_name(rs.getString("good_name"));
            de.setDetail(rs.getString("detail"));
        }
        return de;
    }
    //查询区域和店铺
    public List<AreaEntity> queryShop()throws ClassNotFoundException,SQLException{
        String sql="SELECT s.shop_address shop_address,a.area_name area_name FROM itlb_offline_shops s,itlb_areas a\n" +
                " WHERE a.id=s.area_id;";
       IRowMapper<AreaEntity> rowMapper=new IRowMapper<AreaEntity>() {
           @Override
           public AreaEntity mapper(ResultSet rs) throws SQLException, ClassNotFoundException {
               AreaEntity ae=new AreaEntity();
               ae.setShop_address(rs.getString("shop_address"));
               ae.setArea_name(rs.getString("area_name"));
               return ae;
           }
       };
       List<AreaEntity> list=dbHelper.queryAllHelper(rowMapper,sql);
        return list;
    }
    //注册
    public int insert(String userAccount,String userPassword)throws ClassNotFoundException,SQLException{
        String sql="INSERT INTO users (userAccount,userPassword) VALUES (?,?);";
        return dbHelper.executeUpdate(sql,userAccount,userPassword);
    }
    //登录
    public UserEntity queryAccount(String userAccount,String userPwd)throws ClassNotFoundException,SQLException{
        String sql="select userAccount,userPassword from users";
        ResultSet rs=dbHelper.executequery(sql,userAccount,userPwd);
        UserEntity ue=new UserEntity();
        if(rs.next()){
            ue.setUserAccount(rs.getString("userAccount"));
            ue.setUserPassword(rs.getString("userPassword"));
        }
        return ue;
    }

    /**
     * 购物车部分
     * @throws SQLException
     */
    //查询用户的商品
    public List<GoodsEntity> queryAll()throws ClassNotFoundException,SQLException{
        String sql="SELECT s.id id,s.good_name good_name,s.price price,s.picUrl picUrl,c.counts counts FROM\n" +
                "(SELECT * FROM itlb_goods g\n" +
                " INNER JOIN itlb_goods_pic p ON g.id=p.goodsId)s INNER JOIN itlb_shop_cars c ON\n" +
                "  s.goodsId=c.good_id; ";
        IRowMapper<GoodsEntity> rowMapper=new IRowMapper<GoodsEntity>() {
            @Override
            public GoodsEntity mapper(ResultSet rs) throws SQLException, ClassNotFoundException {
                GoodsEntity ge=new GoodsEntity();
                ge.setId(rs.getLong("id"));
                ge.setCounts(rs.getInt("counts"));
                ge.setGood_name(rs.getString("good_name"));
                ge.setPicUrl(rs.getString("picUrl"));
                ge.setPrice(rs.getDouble("price"));
                return ge;
            }
        };
        List<GoodsEntity> list=dbHelper.queryAllHelper(rowMapper,sql);
        return list;
    }

    //根据商品的id查询商品信息
    public GoodsEntity queryById(long goodsId)throws ClassNotFoundException,SQLException{
        String sql="SELECT s.id id,s.good_name good_name,s.price price,s.picUrl picUrl,c.counts counts FROM\n" +
                "(SELECT * FROM itlb_goods g\n" +
                " INNER JOIN itlb_goods_pic p ON g.id=p.goodsId)s INNER JOIN itlb_shop_cars c ON\n" +
                "  s.goodsId=c.good_id AND s.id=?";
        ResultSet rs=dbHelper.executequery(sql,goodsId);
        GoodsEntity ge=new GoodsEntity();
        if(rs.next()){
            ge.setId(rs.getLong("id"));
            ge.setCounts(rs.getInt("counts"));
            ge.setGood_name(rs.getString("good_name"));
            ge.setPicUrl(rs.getString("picUrl"));
            ge.setPrice(rs.getDouble("price"));
        }
        return ge;
    }
    //修改数量
    public int updateCount(long goodsId,int counts)throws ClassNotFoundException,SQLException{
        String sql="  UPDATE itlb_shop_cars SET counts=? WHERE good_id=?";
        return dbHelper.executeUpdate(sql,counts,goodsId);
    }
    //修改数量时的查询
    public GoodsEntity queryCount(long goodsId)throws ClassNotFoundException,SQLException{
        String sql="SELECT sale_price,counts FROM itlb_shop_cars WHERE good_id=?";
        ResultSet rs=dbHelper.executequery(sql,goodsId);
        GoodsEntity ge=new GoodsEntity();
        if(rs.next()){
            ge.setCounts(rs.getInt("counts"));
            ge.setSale_price(rs.getDouble("sale_price"));
        }
        return ge;
    }

    //关闭
    public void close()throws SQLException{
        dbHelper.close();
    }
}
